package achievement.dao.Impl;

import achievement.dao.SzlStandardDao;
import achievement.domain.SzlStandard;
import cn.hutool.core.util.StrUtil;
import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class SzlStandardDaoImpl implements SzlStandardDao {
    private Connection con;
    private PreparedStatement psmt;
    private ResultSet rs;
    @Override
    public void insert(SzlStandard szlStandard) {
        String sql = "insert into szl_standard\n" +
                "        (standard_name,standard_level,standard_time,standard_proof) \n" +
                "values(?,?,?,?)";
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            psmt.setString(1,szlStandard.getStandardName());
            psmt.setString(2,szlStandard.getStandardLevel());
            psmt.setObject(3,szlStandard.getStandardTime());
            psmt.setString(4,szlStandard.getStandardProof());
            psmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt);
        }
    }

    @Override
    public void updateById(SzlStandard szlStandard) {
        String sql = "update szl_standard set";
        List<Object> param = new ArrayList<>();
        if (szlStandard == null){
            return;
        }else{
            if(szlStandard.getStandardId() != null){
                sql += " standard_id = ?,";
                param.add(szlStandard.getStandardId());
            }
            if(szlStandard.getStandardName() != null){
                sql += " standard_name = ?,";
                param.add(szlStandard.getStandardName());
            }
            if(szlStandard.getType() != null){
                sql += " type = ?,";
                param.add(szlStandard.getType());
            }
            if(szlStandard.getStandardLevel() != null){
                sql += " standard_level = ?,";
                param.add(szlStandard.getStandardLevel());
            }
            if(szlStandard.getStandardTime() != null) {
                sql += " standard_time = ?,";
                param.add(szlStandard.getStandardTime());
            }
            if(szlStandard.getStandardProof() != null){
                sql += " standard_proof = ?,";
                param.add(szlStandard.getStandardProof());
            }
        }
        sql = StrUtil.strip(sql,null,",");
        sql+=" where standard_id = ?";
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            for(int i = 0; i < param.size(); i++){
                psmt.setObject(i+1,param.get(i));
            }
            psmt.setLong(param.size()+1,szlStandard.getStandardId());
            psmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt);
        }
    }

    @Override
    public void deleteById(Long id) {
            String sql = "delete from szl_standard where standard_id = ?";
            try {
                con = JDBCUtils.getConnection();
                psmt = con.prepareStatement(sql);
                psmt.setLong(1,id);
                psmt.executeUpdate();
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                JDBCUtils.closeResource(con,psmt);
            }
    }

    @Override
    public SzlStandard selectById(Long id) {
        String sql = "select * from szl_standard where standard_id = ?";
        SzlStandard szlStandard = new SzlStandard();
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            psmt.setLong(1,id);
            rs = psmt.executeQuery();
            while (rs.next()){
                szlStandard.setStandardId(rs.getLong(1));
                szlStandard.setStandardName(rs.getString(2));
                szlStandard.setType(rs.getInt(3));
                szlStandard.setStandardLevel(rs.getString(4));
                szlStandard.setStandardTime(rs.getDate(5));
                szlStandard.setStandardProof(rs.getString(6));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt,rs);
        }
        return szlStandard;
    }

    @Override
    public List<SzlStandard> selectList(SzlStandard szlStandard) {
        String sql = "select * from szl_standard where 1=1";
        List<SzlStandard> list = new ArrayList<>();
        List<Object> param = new ArrayList<>();
        if (szlStandard == null){
            sql = "select * from szl_standard";
        }else{
            if(szlStandard.getStandardId() != null){
                sql += " and standard_id = ?";
                param.add(szlStandard.getStandardId());
            }
            if(szlStandard.getStandardName() != null){
                sql += " and standard_name like \"%\"?\"%\"";
                param.add(szlStandard.getStandardName());
            }
            if(szlStandard.getType() != null){
                sql += " and type = ?";
                param.add(szlStandard.getType());
            }
            if(szlStandard.getStandardLevel() != null){
                sql += " and standard_level like \"%\"?\"%\"";
                param.add(szlStandard.getStandardLevel());
            }
            if(szlStandard.getStandardTime() != null) {
                sql += " and standard_time = ?";
                param.add(szlStandard.getStandardTime());
            }
            if(szlStandard.getStandardProof() != null){
                sql += " and standard_proof like \"%\"?\"%\"";
                param.add(szlStandard.getStandardProof());
            }
        }
        try {
            con = JDBCUtils.getConnection();
            psmt = con.prepareStatement(sql);
            for(int i = 0; i < param.size(); i++){
                psmt.setObject(i+1,param.get(i));
            }
            rs = psmt.executeQuery();
            while (rs.next()){
                SzlStandard szlStandard1 = new SzlStandard();
                szlStandard1.setStandardId(rs.getLong(1));
                szlStandard1.setStandardName(rs.getString(2));
                szlStandard1.setType(rs.getInt(3));
                szlStandard1.setStandardLevel(rs.getString(4));
                szlStandard1.setStandardTime(rs.getDate(5));
                szlStandard1.setStandardProof(rs.getString(6));
                list.add(szlStandard1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(con,psmt,rs);
        }
        return list ;
    }
}
